package com.gg.jdbc.generate;

import com.gg.jdbc.DB2Connection;
import com.gg.jdbc.generate.SysIndexes.IndexColumn;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

/**
 * <p>This class will generate Java code to read DB2 tables.</p>
 * 
 * @author Gilbert G. Le Blanc
 *
 */

public class GenerateTables {
	
	protected static final String DELIM_LINE = System.getProperty("line.separator");
	
	protected int count;
	
	protected String creator;
	protected String database;
	protected String password;
	protected String user;
	
	public GenerateTables(String database, String user, String password, String creator) {
		this.database = database;
		this.user     = user;
		this.password = password;
		this.creator  = creator;
		this.count    = 0;
	}
	
	public void setConnection() {
		boolean valid = true;
		
		DB2Connection db2 = new DB2Connection(database, user, password);
		Connection connection = db2.connect();
		if (connection == null) 
			valid = false;
		else {
			System.out.println("**** Created a JDBC connection to the mainframe DB2 " + database);
			
			try {
				SysTables tables = new SysTables(connection, creator);
				tables.execSQL();
				String[] tableNames = tables.getTableNames();
				for (int i = 0; i < tableNames.length; i++) {
					createJavaClass(db2, connection, creator, tableNames[i]);
					System.out.println("**** Class " + tableNames[i] + 
							".java created, count " + ++count);
				}
			} catch (SQLException e) {
				DB2Connection.sqlException(e);
				valid = false;
			}
			
			if (db2.commit())
				System.out.println("**** Transaction committed");
			else
				valid = false;
			
			if (db2.disconnect())
				System.out.println("**** Closed connection to the mainframe DB2 " + database);
			else
				valid = false;
		} 
		
		if (valid)
			System.out.println("**** Exit from class DB2Connection - no errors");
		else
			System.out.println("**** Exit from class DB2Connection - errors");
	}
	
	protected boolean createJavaClass(DB2Connection db2, Connection connection, 
			String creator, String table) throws SQLException {
		DateFormat df = DateFormat.getDateTimeInstance(DateFormat.FULL, DateFormat.SHORT);
		Date date = new Date();
		
		SysColumns columns = new SysColumns(connection, creator, table);
		if (!columns.execSQL()) return false;
		StringBuffer stringbufferSelect = columns.getStringBufferSelect();
		
		SysIndexes indexes = new SysIndexes(connection, creator, table);
		if (!indexes.execSQL()) return false;
		
		String packageName = db2.getClass().getPackage().getName();
		
		StringBuffer sb = new StringBuffer();
		sb.append(generatePackage(packageName));
		sb.append(generateImports());
		sb.append(generateClassComments(connection, indexes, creator, table, df, date));		
		sb.append(generateClassOpening(table));
		sb.append(generateInstanceVariables(indexes, table));
		sb.append(generateClassConstructor(table));
		sb.append(generateSetAdditionalSQLMethod(table));
		sb.append(generateExecuteSelectMethods(indexes, table));
		sb.append(generateCloseSelectMethods(indexes, table));
		sb.append(generatePrepareSelectMethods(indexes, stringbufferSelect, table));
		sb.append(generateClassClosing(table));
		
//		System.out.print(sb.toString());
		
		try {
			String fileName;
			if (packageName != null)
				fileName = "src\\" + packageName.replace('.', '\\') + 
					"\\" + table + ".java";
			else
				fileName = "src\\" + table + ".java";
			BufferedWriter outputClass = new BufferedWriter(new FileWriter(fileName));
			outputClass.write(sb.toString());
			outputClass.close();
			return true;
		} catch (IOException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	protected StringBuffer generatePackage(String packageName) {
		StringBuffer sb = new StringBuffer();
		if (packageName != null) {
			sb.append("package ");
			sb.append(packageName);
			sb.append(";");
			sb.append(DELIM_LINE);
			sb.append(DELIM_LINE);
		}
		return sb;
	}
	
	protected StringBuffer generateImports() {
		StringBuffer sb = new StringBuffer();
		sb.append("import java.sql.Connection;");
		sb.append(DELIM_LINE);
		
		sb.append("import java.sql.PreparedStatement;");
		sb.append(DELIM_LINE);
		
		sb.append("import java.sql.ResultSet;");
		sb.append(DELIM_LINE);

		sb.append("import java.sql.SQLException;");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		return sb;
	}
	
	protected StringBuffer generateClassComments(Connection connection,
			SysIndexes indexes, String creator, String table, 
			DateFormat df, Date date) throws SQLException {
		Map<String, IndexColumn[]> indexMap = indexes.getIndexes();
		Set<String> keySet = indexMap.keySet();
		
		StringBuffer sb = new StringBuffer();
		sb.append("/**");
		sb.append(DELIM_LINE);
		sb.append(" * <p>This Java class was generated from the SYSIBM tables in");
		sb.append(DELIM_LINE);
		sb.append(" * the DB2 database.  The columns and indexes were read");
		sb.append(DELIM_LINE);
		sb.append(" * from SYSIBM.SYSCOLUMNS and SYSIBM.SYSINDEXES,");
		sb.append(DELIM_LINE);
		sb.append(" * respectively.  This ensures that the columns and");
		sb.append(DELIM_LINE);
		sb.append(" * indexes reflect the DB2 database definitions at the");
		sb.append(DELIM_LINE);
		sb.append(" * time that this class was generated.</p>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * <p>This class has methods to select all of the rows of");
		sb.append(DELIM_LINE);
		sb.append(" * the ");
		sb.append(table);
		sb.append(" table, or to select certain rows based on");
		sb.append(DELIM_LINE);
		sb.append(" * any of the indexes defined to the DB2 database.</p>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * <p>All of the select methods allow you to add additional");
		sb.append(DELIM_LINE);
		sb.append(" * SQL clauses to the select by using the");
		sb.append(DELIM_LINE);
		sb.append(" * <code>setAdditionalSQL</code> method.  One example of an optional");
		sb.append(DELIM_LINE);
		sb.append(" * clause is the ORDER BY clause.  Another");
		sb.append(DELIM_LINE);
		sb.append(" * example of an optional clause would be the");
		sb.append(DELIM_LINE);
		sb.append(" * WHERE clause, when used with the full table");
		sb.append(DELIM_LINE);
		sb.append(" * select.  This allows you to use a non-indexed ");
		sb.append(DELIM_LINE);
		sb.append(" * or partial indexed WHERE clause with your");
		sb.append(DELIM_LINE);
		sb.append(" * select.</p>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * <p>The generated method names are arbitrary.  There");
		sb.append(DELIM_LINE);
		sb.append(" * will always be an <code>executeFullSelect</code> method,");
		sb.append(DELIM_LINE);
		sb.append(" * which will select all of the rows in the table, unless");
		sb.append(DELIM_LINE);
		sb.append(" * you have added an additional WHERE clause to the select.</p>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * <p>If the table has indexes defined, there will be one or");
		sb.append(DELIM_LINE);
		sb.append(" * more <code>executeIndexnnSelect</code> methods, where");
		sb.append(DELIM_LINE);
		sb.append(" * the nn usually starts with 00 and increments.  The numbers come");
		sb.append(DELIM_LINE);
		sb.append(" * from the SYSIBM.SYSINDEXES table, and are not guaranteed");
		sb.append(DELIM_LINE);
		sb.append(" * to be sequential.</p>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		
		if (!(indexMap.containsKey(SysIndexes.DUMMY_INDEX))) {
			sb.append(DELIM_LINE);
			sb.append(" * <p>Here are the <code>executeIndexnnSelect</code> methods generated for");
			sb.append(DELIM_LINE);
			sb.append(" * the ");
			sb.append(table);
			sb.append(" table.</p>");
			sb.append(DELIM_LINE);
			sb.append(" * <table cellpadding=\"4\" rules=\"all\">");
			sb.append(DELIM_LINE);
			sb.append(" * <tr><th>Method</th><th>Where clause</th></tr>");
			sb.append(DELIM_LINE);
			
			Iterator<String> iterator = keySet.iterator();
			while (iterator.hasNext()) {
				String key = iterator.next();
				String indexName = generateIndexName(table, key, "index");
				sb.append(" * <tr><td><code>execute");
				sb.append(indexName);
				sb.append("Select</code></td>");
				sb.append(DELIM_LINE);
				sb.append(" * <td>");
				String[] clauses = indexes.getStringArrayWhere(key);
				String command = "<code>";
				for (int i = 0; i < clauses.length; i++) {
					sb.append(command);
					sb.append(clauses[i].trim());
					sb.append("</code>");
					command = DELIM_LINE + " * <br /><code>";
				}
				sb.append("</td></tr>");
				sb.append(DELIM_LINE);
			}
			
			sb.append(" * </table>");
			sb.append(DELIM_LINE);
			sb.append(" *");
			sb.append(DELIM_LINE);
			sb.append(" * <p>The question marks in the WHERE clause are");
			sb.append(DELIM_LINE);
			sb.append(" * replaced by the parameters that you pass to");
			sb.append(DELIM_LINE);
			sb.append(" * the method.</p>");
			sb.append(DELIM_LINE);
		}
		
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * <p>The execute methods return a ResultSet.  A ResultSet");
		sb.append(DELIM_LINE);
		sb.append(" * is similar to a DB2 cursor in a Cobol program.  You");
		sb.append(DELIM_LINE);
		sb.append(" * fetch the individual rows of a ResultSet using the");
		sb.append(DELIM_LINE);
		sb.append(" * ResultSet <code>next()</code> method.");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		
		sb.append(" * <p>Once you're finished with a ResultSet, you have to close");
		sb.append(DELIM_LINE);
		sb.append(" * the execution and close the ResultSet.  Otherwise, Java SQL resources");
		sb.append(DELIM_LINE);
		sb.append(" * are consumed, and you'll get a strange -805 SQLCODE when you");
		sb.append(DELIM_LINE);
		sb.append(" * test your class.</p>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		
		sb.append(" * <p>Here's a snippet of Java code showing how you would use");
		sb.append(DELIM_LINE);
		sb.append(" * these generated classes.  This snippet uses the INMT, ICUR,");
		sb.append(DELIM_LINE);
		sb.append(" * and FACL classes.</p>");
		sb.append(DELIM_LINE);
		sb.append(" * <pre><code>");
		sb.append(DELIM_LINE);
		sb.append(" * INMT inmt = new INMT(connection, creator);");
		sb.append(DELIM_LINE);
		sb.append(" * ICUR icur = new ICUR(connection, creator);");
		sb.append(DELIM_LINE);
		sb.append(" * FACL facl = new FACL(connection, creator);");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * ResultSet inmtSet = inmt.executeFullSelect();");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * while (inmtSet.next()) {");
		sb.append(DELIM_LINE);
		sb.append(" *     StringBuffer sb = new StringBuffer();");
		sb.append(DELIM_LINE);
		sb.append(" *     sb.append(\"  WHERE ICUR_FK_INMT = \");");
		sb.append(DELIM_LINE);
		sb.append(" *     sb.append(inmtSet.getInt(\"INTKEY\"));");
		sb.append(DELIM_LINE);
		sb.append(" *     sb.append(\"    AND CODE_ICAT = 'ARS'\");");
		sb.append(DELIM_LINE);
		sb.append(" *     icur.setAdditionalSQL(sb.toString());");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" *     ResultSet icurSet = icur.executeFullSelect();");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" *     while (icurSet.next()) {");
		sb.append(DELIM_LINE);
		sb.append(" *         ResultSet faclSet = ");
		sb.append(DELIM_LINE);
		sb.append(" *             facl.executeIndex00Select");
		sb.append(DELIM_LINE);
		sb.append(" *             (icurSet.getString(\"CODE_FACL\"));");
		sb.append(DELIM_LINE);
		sb.append(" *         faclSet.next();");
		sb.append(DELIM_LINE);
		sb.append(" *         generateDisplayLine");
		sb.append(DELIM_LINE);
		sb.append(" *             (inmtSet, icurSet, faclSet);");
		sb.append(DELIM_LINE);
		sb.append(" *         facl.closeIndex00Select();");
		sb.append(DELIM_LINE);
		sb.append(" *         faclSet.close();");
		sb.append(DELIM_LINE);
		sb.append(" *     }");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" *     icur.closeFullSelect();");
		sb.append(DELIM_LINE);
		sb.append(" *     icurSet.close();");
		sb.append(DELIM_LINE);
		sb.append(" * }");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * inmt.closeFullSelect();");
		sb.append(DELIM_LINE);
		sb.append(" * inmtSet.close();");
		sb.append(DELIM_LINE);
		sb.append(" * </code></pre>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * <p>The method<code>generateDisplayLine</code> in the");
		sb.append(DELIM_LINE);
		sb.append(" * code snippet is a simple method to get columns from");
		sb.append(DELIM_LINE);
		sb.append(" * the table ResultSets and display them.</p>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		
		sb.append(" * <p>This class was generated on ");
		sb.append(df.format(date));
		sb.append(DELIM_LINE);
		sb.append(" * using the DB2 ");
		
		String database = connection.getMetaData().getURL();
		int pos = database.lastIndexOf(":");
		if (pos > 0) database = database.substring(pos + 1);
		
		sb.append(database);
		sb.append(" database connection, creator ");
		sb.append(creator);
		sb.append(",");
		sb.append(DELIM_LINE);
		sb.append(" * DB2 version ");
		sb.append(connection.getMetaData().getDatabaseMajorVersion());
		sb.append(".");
		sb.append(connection.getMetaData().getDatabaseMinorVersion());
		sb.append("</p>");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * @see java.sql.ResultSet");
		sb.append(DELIM_LINE);
		sb.append(" *");
		sb.append(DELIM_LINE);
		sb.append(" * @author Gilbert G. Le Blanc");
		sb.append(DELIM_LINE);
		sb.append(" */");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		return sb;
	}
	
	protected StringBuffer generateClassOpening(String table) {
		StringBuffer sb = new StringBuffer();
		sb.append("public class ");
		sb.append(table);
		sb.append(" {");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		return sb;
	}
	
	protected StringBuffer generateInstanceVariables(SysIndexes indexes, 
			String table) {
		Map<String, IndexColumn[]> indexMap = indexes.getIndexes();
		Set<String> keySet = indexMap.keySet();
		
		StringBuffer sb = new StringBuffer();
		sb.append("\tprotected Connection connection;");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		sb.append("\tprotected PreparedStatement psFull;");
		sb.append(DELIM_LINE);
		
		if (!(indexMap.containsKey(SysIndexes.DUMMY_INDEX))) {		
			Iterator<String> iterator = keySet.iterator();
			while (iterator.hasNext()) {
				String key = iterator.next();
				sb.append("\tprotected PreparedStatement ps");
				sb.append(generateIndexName(table, key, "index"));
				sb.append(";");
				sb.append(DELIM_LINE);
			}
		}
		
		sb.append(DELIM_LINE);

		sb.append("\tprotected String additionalSQL = null;");
		sb.append(DELIM_LINE);
		sb.append("\tprotected String creator;");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		return sb;
	}
	
	protected StringBuffer generateClassConstructor(String table) {
		StringBuffer sb = new StringBuffer();
		sb.append("\t/**");
		sb.append(DELIM_LINE);
		sb.append("\t * This constructor creates an instance of the ");
		sb.append(table);
		sb.append(" class.");
		sb.append(DELIM_LINE);
		sb.append("\t * This allows you to select rows from the DB2 ");
		sb.append(table);
		sb.append(" table.");
		sb.append(DELIM_LINE);
		sb.append("\t *");
		sb.append(DELIM_LINE);
		sb.append("\t * @param connection - A connection from the DB2Connection class");
		sb.append(DELIM_LINE);
		sb.append("\t * @param creator - The creator of the DB2 database, usually");
		sb.append(DELIM_LINE);
		sb.append("\t * J3DVLP, J3TEST, J3VEND, or J3PROD.");
		sb.append(DELIM_LINE);
		sb.append("\t */");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		sb.append("\tpublic ");
		sb.append(table);
		sb.append(" (Connection connection, String creator) {");
		sb.append(DELIM_LINE);
		
		sb.append("\t\tthis.connection = connection;");
		sb.append(DELIM_LINE);
		sb.append("\t\tthis.creator    = creator;");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		sb.append("\t}\t// End ");
		sb.append(table);
		sb.append(" constructor");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		return sb;
	}
	
	protected StringBuffer generateSetAdditionalSQLMethod(String table) {
		StringBuffer sb = new StringBuffer();
		sb.append("\t/**");
		sb.append(DELIM_LINE);
		sb.append("\t * <p>This method allows for additional SQL clauses at the");
		sb.append(DELIM_LINE);
		sb.append("\t * bottom of the select statement.  Additional");
		sb.append(DELIM_LINE);
		sb.append("\t * SQL clauses would be something like");
		sb.append(DELIM_LINE);
		sb.append("\t * ORDER BY.</p>");
		sb.append(DELIM_LINE);
		sb.append("\t *");
		sb.append(DELIM_LINE);
		sb.append("\t * <p>If you need a different WHERE clause");
		sb.append(DELIM_LINE);
		sb.append("\t * than the ");
		sb.append(table);
		sb.append(" table indexes, you can use your own");
		sb.append(DELIM_LINE);
		sb.append("\t * WHERE clause as an additional SQL clause");
		sb.append(DELIM_LINE);
		sb.append("\t * with the Full Select.</p>");
		sb.append(DELIM_LINE);
		sb.append("\t *");
		sb.append(DELIM_LINE);
		sb.append("\t * @param additionalSQL - additional SQL, or <code>null</code>");
		sb.append(DELIM_LINE);
		sb.append("\t */");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		sb.append("\tpublic void setAdditionalSQL(String additionalSQL) {");
		sb.append(DELIM_LINE);
		sb.append("\t\tthis.additionalSQL = additionalSQL;");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		sb.append("\t}\t// End setAdditionalSQL method");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		return sb;
	}
	
	protected StringBuffer generateExecuteSelectMethods
			(SysIndexes indexes, String table) {
		Map<String, IndexColumn[]> indexMap = indexes.getIndexes();
		Set<String> keySet = indexMap.keySet();
		
		StringBuffer sb = new StringBuffer();
		sb.append("\t/**");
		sb.append(DELIM_LINE);
		sb.append("\t * <p>This method executes a select on the entire");
		sb.append(DELIM_LINE);
		sb.append("\t * ");
		sb.append(table);
		sb.append(" table, unless you added a WHERE clause");
		sb.append(DELIM_LINE);
		sb.append("\t * using the <code>setAdditionalSQL</code>");
		sb.append(DELIM_LINE);
		sb.append("\t * method.</p>");
		sb.append(DELIM_LINE);
		sb.append("\t *");
		sb.append(DELIM_LINE);
		sb.append("\t * @return Result set with the results of the select");
		sb.append(DELIM_LINE);
		sb.append("\t */");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		sb.append("\tpublic ResultSet executeFullSelect() {");
		sb.append(DELIM_LINE);
		sb.append(generateExecuteSelectTryBlock("Full", null));
		sb.append(DELIM_LINE);
		
		sb.append("\t}\t// End executeFullSelect method");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		if (!(indexMap.containsKey(SysIndexes.DUMMY_INDEX))) {
			Iterator<String> iterator = keySet.iterator();
			while (iterator.hasNext()) {
				String key = iterator.next();
				String indexName = generateIndexName(table, key, "index");
				sb.append("\t/**");
				sb.append(DELIM_LINE);
				sb.append("\t * <p>This method executes a select using the ");
				sb.append(table);
				sb.append(" ");
				sb.append(indexName);
				sb.append(",");
				sb.append(DELIM_LINE);
				sb.append("\t * which uses the where clause:</p>");
				sb.append(DELIM_LINE);
				sb.append("\t * <pre>");
				sb.append(DELIM_LINE);
				String[] clauses = indexes.getStringArrayWhere(key);
				for (int i = 0; i < clauses.length; i++) {
					sb.append("\t * <code>");
					sb.append(clauses[i]);
					sb.append("</code>");
					sb.append(DELIM_LINE);
				}
				sb.append("\t * </pre>");
				sb.append(DELIM_LINE);
				sb.append("\t * @return Result set with the results of the select");
				sb.append(DELIM_LINE);
				sb.append("\t */");
				sb.append(DELIM_LINE);
				sb.append(DELIM_LINE);
				
				sb.append("\tpublic ResultSet execute");
				sb.append(indexName);
				sb.append("Select(");
				sb.append(indexes.getStringBufferParameters(key));
				sb.append(") {");
				sb.append(DELIM_LINE);
				sb.append(generateExecuteSelectTryBlock(indexName, indexes.getStringBufferVariables(key)));
				sb.append(DELIM_LINE);
				
				sb.append("\t}\t// End execute");
				sb.append(indexName);
				sb.append("Select method");
				sb.append(DELIM_LINE);
				sb.append(DELIM_LINE);
			}
		}
		
		return sb;
	}
	
	protected StringBuffer generatePrepareSelectMethods
			(SysIndexes indexes, StringBuffer stringbufferSelect, String table) {
		Map<String, IndexColumn[]> indexMap = indexes.getIndexes();
		Set<String> keySet = indexMap.keySet();
		
		StringBuffer sb = new StringBuffer();
		sb.append("\tprotected void prepareFullSelect()");
		sb.append(DELIM_LINE);
		sb.append("\t\t\tthrows SQLException {");
		sb.append(DELIM_LINE);
		sb.append(stringbufferSelect);
		sb.append("\t\tif (additionalSQL != null) sb.append(additionalSQL);");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		sb.append(generatePrepareSelectCode("Full", null));
		sb.append(DELIM_LINE);
		
		sb.append("\t}\t// End prepareFullSelect method");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);
		
		if (!(indexMap.containsKey(SysIndexes.DUMMY_INDEX))) {
			Iterator<String> iterator = keySet.iterator();
			while (iterator.hasNext()) {
				String key = iterator.next();
				IndexColumn[] index = indexMap.get(key);
				String indexName = generateIndexName(table, key, "index");
				sb.append("\tprotected void prepare");
				sb.append(indexName);
				sb.append("Select(");
				sb.append(indexes.getStringBufferParameters(key));
				sb.append(")");
				sb.append(DELIM_LINE);
				sb.append("\t\t\tthrows SQLException {");
				sb.append(DELIM_LINE);
				sb.append(stringbufferSelect);
				sb.append(indexes.getStringBufferWhere(key));
				sb.append("\t\tif (additionalSQL != null) sb.append(additionalSQL);");
				sb.append(DELIM_LINE);
				sb.append(DELIM_LINE);
				sb.append(generatePrepareSelectCode(indexName, index));
				sb.append(DELIM_LINE);
				
				sb.append("\t}\t// End prepare");
				sb.append(indexName);
				sb.append("Select method");
				sb.append(DELIM_LINE);
				sb.append(DELIM_LINE);
			}
		}
		
		return sb;
	}
	
	protected StringBuffer generateCloseSelectMethods(SysIndexes indexes,
			String table) {
		Map<String, IndexColumn[]> indexMap = indexes.getIndexes();
		Set<String> keySet = indexMap.keySet();

		StringBuffer sb = new StringBuffer();
		sb.append("\t/**");
		sb.append(DELIM_LINE);
		sb.append("\t * <p>This method closes an execution on the full");
		sb.append(DELIM_LINE);
		sb.append("\t * select method of the ");
		sb.append(table);
		sb.append(" table.</p>");
		sb.append(DELIM_LINE);
		sb.append("\t */");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);

		sb.append("\tpublic void closeFullSelect() throws SQLException {");
		sb.append(DELIM_LINE);
		sb.append("\t\tpsFull.close();");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);

		sb.append("\t}\t// End closeFullSelect method");
		sb.append(DELIM_LINE);
		sb.append(DELIM_LINE);

		if (!(indexMap.containsKey(SysIndexes.DUMMY_INDEX))) {
			Iterator<String> iterator = keySet.iterator();
			while (iterator.hasNext()) {
				String key = iterator.next();
				String indexName = generateIndexName(table, key, "index");
				sb.append("\t/**");
				sb.append(DELIM_LINE);
				sb.append("\t * <p>This method closes a select execution using the ");
				sb.append(DELIM_LINE);
				sb.append("\t * ");
				sb.append(table);
				sb.append(" execute");
				sb.append(indexName);
				sb.append("Select method.</p>");
				sb.append(DELIM_LINE);
				sb.append("\t */");
				sb.append(DELIM_LINE);
				sb.append(DELIM_LINE);

				sb.append("\tpublic void close");
				sb.append(indexName);
				sb.append("Select() throws SQLException {");
				sb.append(DELIM_LINE);
				sb.append("\t\tps");
				sb.append(indexName);
				sb.append(".close();");
				sb.append(DELIM_LINE);
				sb.append(DELIM_LINE);

				sb.append("\t}\t// End close");
				sb.append(indexName);
				sb.append("Select method");
				sb.append(DELIM_LINE);
				sb.append(DELIM_LINE);
			}
		}

		return sb;
	}
	
	protected StringBuffer generateClassClosing(String table) {
		StringBuffer sb = new StringBuffer();
		sb.append("}\t// End ");
		sb.append(table);
		sb.append(" class");
		sb.append(DELIM_LINE);
		
		return sb;
	}
	
	protected String generateIndexName(String table, String index, String name) {
		try {
			String temp;
			if (table.equals("TEMP_AVIS")) temp = "0" + index.substring(7, 8);
			else if (table.equals("THUMBNAIL")) temp = index.substring(6, 8);
			else temp = index.substring(4, 6);
			return Character.toUpperCase(name.charAt(0)) + name.substring(1) + temp;
		} catch (StringIndexOutOfBoundsException e) {
			System.err.println("Table " + table + ", index " + index);
			e.printStackTrace();
		}
		return Character.toUpperCase(name.charAt(0)) + name.substring(1);
	}
	
	protected StringBuffer generateExecuteSelectTryBlock(String ps, StringBuffer variables) {
		StringBuffer sb = new StringBuffer();
		sb.append("\t\ttry {");
		sb.append(DELIM_LINE);
		sb.append("\t\t\tprepare");
		sb.append(ps);
		sb.append("Select(");
		if (variables != null) {
			sb.append(variables);
		}
		sb.append(");");
		sb.append(DELIM_LINE);
		sb.append("\t\t\tResultSet rs = ps");
		sb.append(ps);
		sb.append(".executeQuery();");
		sb.append(DELIM_LINE);
		sb.append("\t\t\treturn rs;");
		sb.append(DELIM_LINE);
		sb.append("\t\t} catch (SQLException e) {");
		sb.append(DELIM_LINE);
		sb.append("\t\t\tif (e.getErrorCode() == +100) return null;");
		sb.append(DELIM_LINE);
		sb.append("\t\t\tDB2Connection.sqlException(e);");
		sb.append(DELIM_LINE);
		sb.append("\t\t}");
		sb.append(DELIM_LINE);
		sb.append("\t\treturn null;");
		sb.append(DELIM_LINE);
		return sb;
	}

	protected StringBuffer generatePrepareSelectCode(String ps, IndexColumn[] index) {
		StringBuffer sb = new StringBuffer();
		sb.append("\t\tps");
		sb.append(ps);
		sb.append(" = connection.prepareStatement(sb.toString());");
		sb.append(DELIM_LINE);
		if (index != null) {
			for (int i = 0; i < index.length; i++) {
				sb.append("\t\tps");
				sb.append(ps);
				String test = index[i].getJavaColumnType();
				if      (test.equals("String")) sb.append(".setString(");
				else if (test.equals("int"))    sb.append(".setInt(");
				else if (test.equals("short"))  sb.append(".setShort(");
				else if (test.equals("java.math.BigDecimal")) sb.append(".setBigDecimal(");
				else if (test.equals("java.sql.Timestamp")) sb.append(".setTimestamp(");
				else if (test.equals("java.sql.Date")) sb.append(".setDate(");
				else if (test.equals("java.sql.Time")) sb.append(".setTime(");
				else {
					sb.append(".set");
					sb.append(test);
					sb.append("(");
				}
				sb.append(i + 1);
				sb.append(", ");
				sb.append(index[i].getJavaColumnName());
				sb.append(");");
				sb.append(DELIM_LINE);
			}
		}
	
		return sb;
	}
	
	public static void main(String[] args) {
		System.out.println("**** Enter class GenerateTables");
		
		if (args.length < 4) {
			System.err.println("Not enough arguments.");
			System.err.println("The first argument appended to "
					+ "jdbc:db2: must specify a valid DB2 URL.");
			System.err.println("The second argument is the user.");
			System.err.println("The third argument is the password.");
			System.err.println("The fourth argument is the database creator.");
			System.exit(1);
		}
		
		new GenerateTables(args[0], args[1], args[2], args[3]).setConnection();
		
	} // End main
	
} // End GenerateTables